#!/bin/bash
# author: onion
# date: 2025年7月7日15:30:33
# description： 删除分布式表

# ClickHouse 安全删除所有表的脚本
CK_USERNAME=default
CK_PASSWORD=shsnc!@#
CK_DBNAME=snc_aiops_performance
CLUSTER_NAME="cluster_3shards_1replicas"

CK_CLIEN="clickhouse-client --port 9000 --user ${CK_USERNAME} --password ${CK_PASSWORD} --database ${CK_DBNAME}"

# 1. 检查数据库是否存在
if ! ${CK_CLIEN} --query "EXISTS DATABASE ${CK_DBNAME}" >/dev/null; then
    echo "错误：数据库 ${CK_DBNAME} 不存在!"
    exit 1
fi

echo "===== 开始删除数据库 ${CK_DBNAME} 中的所有表 ====="

# 2. 获取所有视图并删除
echo "正在处理视图..."
all_views=$(${CK_CLIEN} --query "
    SELECT name
    FROM system.tables
    WHERE database = '${CK_DBNAME}'
      AND engine IN ('View', 'MaterializedView')
")
view_array=($all_views)

for view in "${view_array[@]}"; do
    echo "删除视图: ${CK_DBNAME}.${view} ON CLUSTER ${CLUSTER_NAME}"
    ${CK_CLIEN} --query "DROP VIEW IF EXISTS ${CK_DBNAME}.${view} ON CLUSTER ${CLUSTER_NAME} SYNC"
    sleep 0.5  # 安全间隔
done

# 3. 获取所有普通表并删除
echo "正在处理普通表..."
all_tables=$(${CK_CLIEN} --query "
    SELECT name
    FROM system.tables
    WHERE database = '${CK_DBNAME}'
      AND engine NOT IN ('View', 'MaterializedView', 'System')
")
tables_array=($all_tables)

for table in "${tables_array[@]}"; do
    echo "删除表: ${CK_DBNAME}.${table} ON CLUSTER ${CLUSTER_NAME}"
    ${CK_CLIEN} --query "DROP TABLE IF EXISTS ${CK_DBNAME}.${table} ON CLUSTER ${CLUSTER_NAME} SYNC"
    sleep 0.5  # 安全间隔
done

# 4. 特殊处理：重建数据库确保元数据同步（可选）
read -p "是否重新创建数据库以清空元数据？(y/n) " recreate
if [[ "$recreate" == "y" ]]; then
    echo "重新创建数据库 ${CK_DBNAME}..."
    ${CK_CLIEN} --query "DROP DATABASE IF EXISTS ${CK_DBNAME} ON CLUSTER ${CLUSTER_NAME} SYNC"
    ${CK_CLIEN} --query "CREATE DATABASE ${CK_DBNAME} ON CLUSTER ${CLUSTER_NAME}"
    echo "数据库 ${CK_DBNAME} 已重建"
fi

echo "===== 所有表已成功删除 ====="
